﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Windows.Forms;
using System.Globalization;
using System.Data.SqlClient;
using DevExpress.XtraEditors;

namespace GenCode
{
    class GenCodeDAL : MySqlProvider
    {
        //private static string m_connect =
        //    @"Data Source=CANHHUNG-PC\SQLEXPRESS;Initial Catalog=master;Integrated Security=True";
        public static void ShowDatabase(DevExpress.XtraEditors.ComboBoxEdit cb)
       {
           string m_connect = "";
          #region RegionName
           string conn = "";
           if (Global1.win)
           {
               conn = Global1.QuyenWin("master");
           }
           else
               conn = Global1.QuyenAuthen("master");
            m_connect = conn;

           cb.Properties.Items.Clear();
           var con = new SqlConnection(m_connect);
           var cmd = new SqlCommand("SP_DATABASES", con);
           SqlDataReader reader;
           try
           {
               con.Open();
               reader = cmd.ExecuteReader();
               while (reader.Read())
               {
                   cb.Properties.Items.Add(reader[0].ToString());
               }
               cb.Enabled = true;
              // XtraMessageBox.Show("Kết nối thành công!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
           }
           catch
           {
               XtraMessageBox.Show("Lỗi, vui lòng thử lại!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
           }
           finally
           {
               if (con.State == ConnectionState.Open)
                   con.Close();
               con.Dispose();
               cmd.Dispose();
           }
          #endregion
        }
        public static void ShowTableName(DevExpress.XtraEditors.ComboBoxEdit cb, string database)
        {
           cb.Properties.Items.Clear();
           string Query =
                          @"SELECT [TABLE_NAME]
                           FROM INFORMATION_SCHEMA.TABLES
                           WHERE table_type = 'BASE TABLE'";
           string conn = "";
           if (Global1.win)
           {
               conn = Global1.QuyenWin(database);
           }
           else
               conn = Global1.QuyenAuthen(database);
           var con = new SqlConnection(conn);
           var cmd = new SqlCommand(Query, con);
           SqlDataReader reader;
           try
           {
               con.Open();
               reader = cmd.ExecuteReader();
               while (reader.Read())
               {
                   cb.Properties.Items.Add(reader[0].ToString());
               }
           }
           catch (SqlException ex)
           {
               XtraMessageBox.Show(ex.Message, "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
           }
           finally
           {
               if (con.State == ConnectionState.Open)
                   con.Close();
               con.Dispose();
               cmd.Dispose();
           }
        }
        public static void ShowColumnName(DevExpress.XtraEditors.ComboBoxEdit cb, string databasename, string tablename)
        {
            cb.Properties.Items.Clear();
            string Query =
                           @"SELECT [Column_NAME],[Data_Type],[CHARACTER_MAXIMUM_LENGTH]
                           FROM INFORMATION_SCHEMA.Columns
                           WHERE Table_Name='" + tablename + "'";
            string conn = "";
            if (Global1.win)
            {
                conn = Global1.QuyenWin(databasename);
            }
            else
                conn = Global1.QuyenAuthen(databasename);
            var con = new SqlConnection(conn);
            var cmd = new SqlCommand(Query, con);
            SqlDataReader reader;
            try
            {
                con.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    #region Kiem tra IsIdentity
                    string sql = @"Select [Column_NAME]
                                   FROM INFORMATION_SCHEMA.Columns
                                   WHERE Table_Name='" + tablename + "' AND COLUMNPROPERTY(OBJECT_ID('" + tablename + "'),'" + reader[0] + "','IsIdentity')=1";
                    bool kt = false;
                    conn = "";
                    if (Global1.win)
                    {
                        conn = Global1.QuyenWin(databasename);
                    }
                    else
                        conn = Global1.QuyenAuthen(databasename);
                    var con2 = new SqlConnection(conn);
                    var command = new SqlCommand(sql, con2);
                    try
                    {
                        con2.Open();
                        kt = command.ExecuteScalar() != null;
                    }
                    catch (Exception)
                    {
                        kt = false;
                    }
                    finally
                    {
                        if (con2.State == ConnectionState.Open)
                            con2.Close();
                        con2.Dispose();
                        command.Dispose();
                    }
                    #endregion
                    #region Kiem tra IsForeignKey

                    string query = @"select constraint_name from Information_schema.CONSTRAINT_COLUMN_USAGE
                                     where table_name='" + tablename + "' and column_name='" + reader[0].ToString() + "'";
                    bool checkFK = false;
                    //string conn = "";
                    if (Global1.win)
                    {
                        conn = Global1.QuyenWin(databasename);
                    }
                    else
                        conn = Global1.QuyenAuthen(databasename);
                    var con3 = new SqlConnection(conn);
                    SqlCommand command3 = null;
                    try
                    {
                        con3.Open();
                        command3 = new SqlCommand(query, con3);
                        if (command3.ExecuteScalar() != null)
                        {
                            string s = command3.ExecuteScalar().ToString();
                            if (s != "")
                                if (s.Substring(0, 2) == "FK")
                                    checkFK = true;
                        }
                    }
                    catch (Exception)
                    {
                        checkFK = false;
                    }
                    finally
                    {
                        if (con3.State == ConnectionState.Open)
                            con3.Close();
                        con3.Dispose();
                        command3.Dispose();
                    }
                    #endregion
                    cb.Properties.Items.Add(reader[0].ToString());
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                XtraMessageBox.Show(ex.Message, "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
                con.Dispose();
                cmd.Dispose();
            }
        }
        public static string CodeInfo(string databasename, string tablename, string name_space)
        {
           try
           {
              string ketqua = "";
              string sql;
              DataTable dt;

              dt = FormDesigner.GetDataTable1(databasename, tablename);
              ketqua = "namespace " + name_space + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "public class " + InfoTableName(tablename) + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              for(int i = 0;i < dt.Rows.Count;i++)
              {
                 ketqua = ketqua + "private ";
                 if(dt.Rows[i]["DATA_TYPE"].ToString() == "tinyint")
                 {
                    ketqua = ketqua + "bool  ";
                    //ketqua = ketqua + "_" + dt.Rows[i]["COLUMN_NAME"].ToString() + " = false;";
                 }
                 else if(dt.Rows[i]["DATA_TYPE"].ToString() == "varchar")
                 {
                    ketqua = ketqua + "string ";
                    //ketqua = ketqua + "_" + dt.Rows[i]["COLUMN_NAME"].ToString() + " = \"\";";
                 }
                 else
                 {
                    ketqua = ketqua + "int ";
                    //ketqua = ketqua + "_" + dt.Rows[i]["COLUMN_NAME"].ToString() + " = 0;";
                 }
                 ketqua = ketqua + "_" + dt.Rows[i]["COLUMN_NAME"].ToString() + ";";
                 ketqua = ketqua + Environment.NewLine;
              }

              for(int i = 0;i < dt.Rows.Count;i++)
              {
                 ketqua = ketqua + "public ";
                 if(dt.Rows[i]["DATA_TYPE"].ToString() == "tinyint")
                 {
                    ketqua = ketqua + "bool ";
                 }
                 if(dt.Rows[i]["DATA_TYPE"].ToString() == "varchar")
                 {
                    ketqua = ketqua + "string ";
                 }
                 if(dt.Rows[i]["DATA_TYPE"].ToString() == "int")
                 {
                    ketqua = ketqua + "int ";
                 }
                 ketqua = ketqua + dt.Rows[i]["COLUMN_NAME"].ToString() + Environment.NewLine;
                 ;
                 ketqua = ketqua + "{" + Environment.NewLine;
                 ketqua = ketqua + "get { return _" + dt.Rows[i]["COLUMN_NAME"].ToString() + "; }" + Environment.NewLine;
                 ketqua = ketqua + "set { _" + dt.Rows[i]["COLUMN_NAME"].ToString() + " = value; }" + Environment.NewLine;
                 ketqua = ketqua + "}" + Environment.NewLine;

              }
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}";
              return ketqua;
           }
           catch(Exception ex)
           {
              XtraMessageBox.Show(ex.Message);
              return "";
           }
        }
        public static string CodeDAL(string databasename, string tablename, string name_space)
        {
           try
           {
              string ketqua = "";
              string sql;
              DataTable dt;

              sql = "select * from information_schema.COLUMNS ";
              sql = sql + "where TABLE_NAME ='" + tablename + "'";
              sql = sql + " and table_schema = '" + databasename + "'";
              dt = FormDesigner.GetDataTable1(databasename, tablename);

              ketqua = ketqua + "using System;" + Environment.NewLine;
              ketqua = ketqua + "using System.Collections.Generic;" + Environment.NewLine;
              ketqua = ketqua + "using System.Text;" + Environment.NewLine;
              ketqua = ketqua + "using System.Threading.Tasks;" + Environment.NewLine;
              ketqua = ketqua + "using System.Data;" + Environment.NewLine;
              ketqua = ketqua + Environment.NewLine;
              ketqua = ketqua + "namespace " + name_space + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "class " + DALTableName(tablename) + " : MySqlProvider" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              //thủ tục select
              ketqua = ketqua + "public DataTable select(string DieuKien)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "string sql = string.Empty;" + Environment.NewLine;
              ketqua = ketqua + "MySqlCommand cmd = null;" + Environment.NewLine;
              ketqua = ketqua + "try" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "sql = \"SELECT ";
              for(int i = 0;i < dt.Rows.Count;i++)
              {
                 if(i == dt.Rows.Count - 1)
                 {
                    ketqua = ketqua + dt.Rows[i]["COLUMN_NAME"];
                 }
                 else
                 {
                    ketqua = ketqua + dt.Rows[i]["COLUMN_NAME"] + ",";
                 }
              }
              ketqua = ketqua + " FROM " + tablename + " \" + DieuKien;" + Environment.NewLine;
              ketqua = ketqua + "cmd = new MySqlCommand(sql, Connect());" + Environment.NewLine;
              ketqua = ketqua + "return GetDataTable(cmd);" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "catch (Exception) { throw; }" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              //thủ tục insert
              ketqua = ketqua + "public void insert(" + InfoTableName(tablename) + " info)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "string sql = string.Empty;" + Environment.NewLine;
              ketqua = ketqua + "MySqlCommand cmd = null;" + Environment.NewLine;
              ketqua = ketqua + "try" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "sql = \"INSERT INTO " + tablename + "(";
              for(int i = 0;i < dt.Rows.Count;i++)
              {
                 if(i == dt.Rows.Count - 1)
                 {
                    ketqua = ketqua + dt.Rows[i]["COLUMN_NAME"] + ") ";
                 }
                 else
                 {
                    ketqua = ketqua + dt.Rows[i]["COLUMN_NAME"] + ", ";
                 }
              }
              ketqua = ketqua + "VALUES(";
              for(int i = 0;i < dt.Rows.Count;i++)
              {
                 if(i == dt.Rows.Count - 1)
                 {
                    ketqua = ketqua + "@" + dt.Rows[i]["COLUMN_NAME"] + ")\";" + Environment.NewLine;
                 }
                 else
                 {
                    ketqua = ketqua + "@" + dt.Rows[i]["COLUMN_NAME"] + ", ";
                 }
              }
              ketqua = ketqua + "cmd = new MySqlCommand(sql, Connect());" + Environment.NewLine;
              ketqua = ketqua + Environment.NewLine;
              for(int i = 0;i < dt.Rows.Count;i++)
              {
                 ketqua = ketqua + "cmd.Parameters.Add(new MySqlParameter(" + "\"" + dt.Rows[i]["COLUMN_NAME"] + "\"" + ", MySqlDbType.";
                 if(dt.Rows[i]["DATA_TYPE"].ToString() == "varchar")
                 {
                    ketqua = ketqua + "VarChar, 255));" + Environment.NewLine;
                 }
                 if(dt.Rows[i]["DATA_TYPE"].ToString() == "int")
                 {
                    ketqua = ketqua + "Int32));" + Environment.NewLine;
                 }
                 if(dt.Rows[i]["DATA_TYPE"].ToString() == "float")
                 {
                    ketqua = ketqua + "Double));" + Environment.NewLine;
                 }
              }
              ketqua = ketqua + Environment.NewLine;
              for(int i = 0;i < dt.Rows.Count;i++)
              {
                 ketqua = ketqua + "cmd.Parameters[" + "\"" + dt.Rows[i]["COLUMN_NAME"] + "\"" + "].Value = info." + dt.Rows[i]["COLUMN_NAME"] + ";" + Environment.NewLine;
              }
              ketqua = ketqua + "ExeCuteNonquery(cmd);";
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "catch (Exception){throw;}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              //thủ tục update
              ketqua = ketqua + "public void update(" + InfoTableName(tablename) + " info)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "string sql = string.Empty;" + Environment.NewLine;
              ketqua = ketqua + "MySqlCommand cmd = null;" + Environment.NewLine;
              ketqua = ketqua + "try" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "sql = \"UPDATE " + tablename + " SET ";
              for(int i = 0;i < dt.Rows.Count;i++)
              {
                 if(dt.Rows[i]["COLUMN_NAME"].ToString() != "ID")
                 {
                    if(i == dt.Rows.Count - 1)
                    {
                       ketqua = ketqua + dt.Rows[i]["COLUMN_NAME"] + "=@" + dt.Rows[i]["COLUMN_NAME"] + " WHERE ID=@ID\";";
                    }
                    else
                    {
                       ketqua = ketqua + dt.Rows[i]["COLUMN_NAME"] + "=@" + dt.Rows[i]["COLUMN_NAME"] + ", ";
                    }

                 }
              }
              ketqua = ketqua + Environment.NewLine;
              ketqua = ketqua + "cmd = new MySqlCommand(sql, Connect());" + Environment.NewLine;
              ketqua = ketqua + Environment.NewLine;
              for(int i = 0;i < dt.Rows.Count;i++)
              {
                 ketqua = ketqua + "cmd.Parameters.Add(new MySqlParameter(" + "\"" + dt.Rows[i]["COLUMN_NAME"] + "\"" + ", MySqlDbType.";
                 if(dt.Rows[i]["DATA_TYPE"].ToString() == "varchar")
                 {
                    ketqua = ketqua + "VarChar, 255));" + Environment.NewLine;
                 }
                 if(dt.Rows[i]["DATA_TYPE"].ToString() == "int")
                 {
                    ketqua = ketqua + "Int32));" + Environment.NewLine;
                 }
                 if(dt.Rows[i]["DATA_TYPE"].ToString() == "float")
                 {
                    ketqua = ketqua + "Double));" + Environment.NewLine;
                 }
              }
              ketqua = ketqua + Environment.NewLine;
              for(int i = 0;i < dt.Rows.Count;i++)
              {
                 ketqua = ketqua + "cmd.Parameters[" + "\"" + dt.Rows[i]["COLUMN_NAME"] + "\"" + "].Value = info." + dt.Rows[i]["COLUMN_NAME"] + ";" + Environment.NewLine;
              }

              ketqua = ketqua + "ExeCuteNonquery(cmd);";
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "catch (Exception){throw;}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;

              //thủ tục delete
              ketqua = ketqua + "public void delete(string dieukien)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "string sql;" + Environment.NewLine;
              ketqua = ketqua + "MySqlCommand cmd = null;" + Environment.NewLine;
              ketqua = ketqua + "try" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "sql = \"DELETE FROM " + tablename + " \" + dieukien;" + Environment.NewLine;
              ketqua = ketqua + "cmd = new MySqlCommand(sql, Connect());" + Environment.NewLine;
              ketqua = ketqua + "ExeCuteNonquery(cmd);" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "catch (Exception) { throw; }" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              return ketqua;
           }
           catch(Exception ex)
           {
              XtraMessageBox.Show(ex.Message);
              return "";
           }
        }
        public static string CodeBUSsingle(string databasename, string tablename, string name_space, string selectbyvalue, string checkcolumn, string checktable)
        {
           try
           {
              string ketqua = "";
              string sql;
              DataTable dt;

              sql = "select * from information_schema.COLUMNS ";
              sql = sql + "where TABLE_NAME ='" + tablename + "'";
              sql = sql + " and table_schema = '" + databasename + "'";
              dt = FormDesigner.GetDataTable1(databasename, tablename);

              ketqua = ketqua + "using System;" + Environment.NewLine;
              ketqua = ketqua + "using System.Collections.Generic;" + Environment.NewLine;
              ketqua = ketqua + "using System.Linq;" + Environment.NewLine;
              ketqua = ketqua + "using System.Text;" + Environment.NewLine;
              ketqua = ketqua + "using System.Threading.Tasks;" + Environment.NewLine;
              ketqua = ketqua + "using System.Data;" + Environment.NewLine;
              ketqua = ketqua + "using DevExpress.XtraGrid;" + Environment.NewLine;
              ketqua = ketqua + Environment.NewLine;
              ketqua = ketqua + "namespace " + name_space + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "class " + BUSTableName(tablename) + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              //Select All
              ketqua = ketqua + "public static void SelectAll(ref DataTable dt, GridControl grd)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + DALTableName(tablename) + " " + DALTableName(tablename).ToLower() + " = new " + DALTableName(tablename) + "();" + Environment.NewLine;
              ketqua = ketqua + "dt = " + DALTableName(tablename).ToLower() + ".select(\"\");" + Environment.NewLine;
              ketqua = ketqua + "grd.DataSource = dt;" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              //SelectByID
              ketqua = ketqua + "public static void SelectByID(string " + selectbyvalue.ToLower() + ", ref DataTable dt, GridControl grd)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + DALTableName(tablename) + " " + DALTableName(tablename).ToLower() + " = new " + DALTableName(tablename) + "();" + Environment.NewLine;
              ketqua = ketqua + "dt = " + DALTableName(tablename).ToLower() + ".select(string.Format(\"WHERE " + selectbyvalue + "='{0}'\", " + selectbyvalue.ToLower() + "));" + Environment.NewLine;
              ketqua = ketqua + "grd.DataSource = dt;" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;

              //Save
              ketqua = ketqua + "public static void Save(ref DataTable dt)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + InfoTableName(tablename) + " " + InfoTableName(tablename).ToLower() + " = new " + InfoTableName(tablename) + "();" + Environment.NewLine;
              ketqua = ketqua + DALTableName(tablename) + " " + DALTableName(tablename).ToLower() + " = new " + DALTableName(tablename) + "();" + Environment.NewLine;
              ketqua = ketqua + "DataTable dtRemine;" + Environment.NewLine;
              ketqua = ketqua + "dtRemine = dt.GetChanges();" + Environment.NewLine;
              ketqua = ketqua + "if (dtRemine != null)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "for (int i = 0; i < dtRemine.Rows.Count; i++)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "if (dtRemine.Rows[i][\"ID\"] == DBNull.Value) //add new" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              for(int i = 0;i < dt.Rows.Count;i++)
              {
                 if(dt.Rows[i]["COLUMN_NAME"].ToString() == "ID")
                 {
                    ketqua = ketqua + InfoTableName(tablename).ToLower() + "." + dt.Rows[i]["COLUMN_NAME"] + " = Algorithm.GenerateRandomID();" + Environment.NewLine;
                 }
                 else
                 {
                    if(dt.Rows[i]["DATA_TYPE"].ToString() == "varchar")
                    {
                       ketqua = ketqua + InfoTableName(tablename).ToLower() + "." + dt.Rows[i]["COLUMN_NAME"] + " = dtRemine.Rows[i][\"" + dt.Rows[i]["COLUMN_NAME"] + "\"].ToString();" + Environment.NewLine;
                    }
                    if(dt.Rows[i]["DATA_TYPE"].ToString() == "int")
                    {
                       ketqua = ketqua + InfoTableName(tablename).ToLower() + "." + dt.Rows[i]["COLUMN_NAME"] + " = Convert.ToInt32(dtRemine.Rows[i][\"" + dt.Rows[i]["COLUMN_NAME"] + "\"]);" + Environment.NewLine;
                    }
                    if(dt.Rows[i]["DATA_TYPE"].ToString() == "tinyint")
                    {
                       ketqua = ketqua + InfoTableName(tablename).ToLower() + "." + dt.Rows[i]["COLUMN_NAME"] + " = Convert.ToBoolean(dtRemine.Rows[i][\"" + dt.Rows[i]["COLUMN_NAME"] + "\"]);" + Environment.NewLine;
                    }
                    if(dt.Rows[i]["DATA_TYPE"].ToString() == "float")
                    {
                       ketqua = ketqua + InfoTableName(tablename).ToLower() + "." + dt.Rows[i]["COLUMN_NAME"] + " = Convert.Double(dtRemine.Rows[i][\"" + dt.Rows[i]["COLUMN_NAME"] + "\"]);" + Environment.NewLine;
                    }
                 }
              }
              ketqua = ketqua + DALTableName(tablename).ToLower() + ".insert(" + InfoTableName(tablename).ToLower() + ");" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "else //update" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              for(int i = 0;i < dt.Rows.Count;i++)
              {
                 if(dt.Rows[i]["DATA_TYPE"].ToString() == "varchar")
                 {
                    ketqua = ketqua + InfoTableName(tablename).ToLower() + "." + dt.Rows[i]["COLUMN_NAME"] + " = dtRemine.Rows[i][\"" + dt.Rows[i]["COLUMN_NAME"] + "\"].ToString();" + Environment.NewLine;
                 }
                 if(dt.Rows[i]["DATA_TYPE"].ToString() == "int")
                 {
                    ketqua = ketqua + InfoTableName(tablename).ToLower() + "." + dt.Rows[i]["COLUMN_NAME"] + " = Convert.ToInt32(dtRemine.Rows[i][\"" + dt.Rows[i]["COLUMN_NAME"] + "\"]);" + Environment.NewLine;
                 }
                 if(dt.Rows[i]["DATA_TYPE"].ToString() == "tinyint")
                 {
                    ketqua = ketqua + InfoTableName(tablename).ToLower() + "." + dt.Rows[i]["COLUMN_NAME"] + " = Convert.ToBoolean(dtRemine.Rows[i][\"" + dt.Rows[i]["COLUMN_NAME"] + "\"]);" + Environment.NewLine;
                 }
                 if(dt.Rows[i]["DATA_TYPE"].ToString() == "float")
                 {
                    ketqua = ketqua + InfoTableName(tablename).ToLower() + "." + dt.Rows[i]["COLUMN_NAME"] + " = Convert.Double(dtRemine.Rows[i][\"" + dt.Rows[i]["COLUMN_NAME"] + "\"]);" + Environment.NewLine;
                 }
              }
              ketqua = ketqua + DALTableName(tablename).ToLower() + ".update(" + InfoTableName(tablename).ToLower() + ");" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              //Delete
              ketqua = ketqua + "public static void Delete(string id)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "try" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + DALTableName(tablename) + " " + DALTableName(tablename).ToLower() + " = new " + DALTableName(tablename) + "();" + Environment.NewLine;
              ketqua = ketqua + DALTableName(tablename).ToLower() + ".delete(string.Format(\"where ID='{0}'\",id));" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "catch (Exception){throw;}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              //CheckBeforeDelete
              ketqua = ketqua + "public static bool CheckBeforeDelete(string " + checkcolumn.ToLower() + ")" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "bool bReturn = false;" + Environment.NewLine;
              ketqua = ketqua + "string sql = string.Empty;" + Environment.NewLine;
              ketqua = ketqua + "DataTable dt = new DataTable();" + Environment.NewLine;
              ketqua = ketqua + "try" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + DALTableName(checktable) + " " + DALTableName(checktable).ToLower() + " = new " + DALTableName(checktable) + "();" + Environment.NewLine;
              ketqua = ketqua + "dt = " + DALTableName(tablename).ToLower() + ".select(string.Format(\"WHERE " + checkcolumn + "='{0}'\", " + checkcolumn.ToLower() + "));" + Environment.NewLine;
              ketqua = ketqua + "if (dt.Rows.Count > 0)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "bReturn = true;" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "return bReturn;" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "catch (Exception){throw;}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              return ketqua;
           }
           catch(Exception ex)
           {
              XtraMessageBox.Show(ex.Message);
              return "";
           }
        }
        public static string Provider(string name_space)
        {
           try
           {
              string ketqua = "";

              ketqua = ketqua + "using System;" + Environment.NewLine;
              ketqua = ketqua + "using System.Collections.Generic;" + Environment.NewLine;
              ketqua = ketqua + "using System.Text;" + Environment.NewLine;
              ketqua = ketqua + "using System.Threading.Tasks;" + Environment.NewLine;
              ketqua = ketqua + "using System.Data;" + Environment.NewLine;
              ketqua = ketqua + Environment.NewLine;
              ketqua = ketqua + "namespace " + name_space + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "public class MySqlProvider" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "private static readonly string strConn = Global.ConnectionString();" + Environment.NewLine;
              ketqua = ketqua + "private static MySqlConnection conn;" + Environment.NewLine;
              ketqua = ketqua + "public static MySqlConnection Connect()" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "if (conn == null)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "conn = new MySqlConnection(strConn);" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "return conn;" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "public static void Open()" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "if (conn.State == ConnectionState.Closed)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "conn.Open();" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "public static void Close()" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "if (conn.State == ConnectionState.Open)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "conn.Close();" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "public static DataTable GetDataTable(string sql)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "DataTable dtTemp = null;" + Environment.NewLine;
              ketqua = ketqua + "MySqlDataAdapter adap = new MySqlDataAdapter(sql, Connect());" + Environment.NewLine;
              ketqua = ketqua + "try" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "Open();" + Environment.NewLine;
              ketqua = ketqua + "dtTemp = new DataTable();" + Environment.NewLine;
              ketqua = ketqua + "adap.Fill(dtTemp);" + Environment.NewLine;
              ketqua = ketqua + "return dtTemp;" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "catch (Exception){return null;}" + Environment.NewLine;
              ketqua = ketqua + "finally{ Close(); adap.Dispose(); dtTemp.Dispose(); }" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "public static DataTable GetDataTable(MySqlCommand cmd)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "DataTable dtTemp = null;" + Environment.NewLine;
              ketqua = ketqua + "MySqlDataAdapter adap = new MySqlDataAdapter(cmd);" + Environment.NewLine;
              ketqua = ketqua + "try" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "Open();" + Environment.NewLine;
              ketqua = ketqua + "dtTemp = new DataTable();" + Environment.NewLine;
              ketqua = ketqua + "adap.Fill(dtTemp);" + Environment.NewLine;
              ketqua = ketqua + "return dtTemp;" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "catch (Exception){return null;}" + Environment.NewLine;
              ketqua = ketqua + "finally{Close();adap.Dispose();dtTemp.Dispose();}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "public static void ExeCuteNonquery(MySqlCommand cmd)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "try" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "Open();" + Environment.NewLine;
              ketqua = ketqua + "cmd.ExecuteNonQuery();" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "catch (MySqlException){}" + Environment.NewLine;
              ketqua = ketqua + "finally{Close();cmd.Dispose();}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;

              return ketqua;
           }
           catch(Exception ex)
           {
              XtraMessageBox.Show(ex.Message);
              return "";
           }
        }
        public static string Global(string database, string name_space)
        {
           try
           {
              string ketqua = "";
              ketqua = ketqua + "using System;" + Environment.NewLine;
              ketqua = ketqua + "using System.Collections.Generic;" + Environment.NewLine;
              ketqua = ketqua + "using System.Text;" + Environment.NewLine;
              ketqua = ketqua + "using System.Threading.Tasks;" + Environment.NewLine;
              ketqua = ketqua + Environment.NewLine;
              ketqua = ketqua + "namespace " + name_space + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "class Global" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "public static string ConnectionString()" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "return \"" + GenCode.Global1.chuoi + "\";" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "public static FormStatus sFormStatus = 0;" + Environment.NewLine;
              ketqua = ketqua + "public static string sIDLinkGridView;" + Environment.NewLine;
              ketqua = ketqua + "public static string sIDOrder;" + Environment.NewLine;
              ketqua = ketqua + "public static string UserName;" + Environment.NewLine;
              ketqua = ketqua + "public enum FormStatus" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "View = 0," + Environment.NewLine;
              ketqua = ketqua + "Addnew = 1," + Environment.NewLine;
              ketqua = ketqua + "Edit = 2," + Environment.NewLine;
              ketqua = ketqua + "Delete = 3" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}";

              return ketqua;
           }
           catch(Exception ex)
           {
              XtraMessageBox.Show(ex.Message);
              return "";
           }
        }

        public static string Algorithm(string name_space)
        {
           try
           {
              string ketqua = "";
              ketqua = ketqua + "using System;" + Environment.NewLine;
              ketqua = ketqua + "using System.Collections.Generic;" + Environment.NewLine;
              ketqua = ketqua + "using System.Data;" + Environment.NewLine;
              ketqua = ketqua + "using System.IO;" + Environment.NewLine;
              ketqua = ketqua + "using System.Text;" + Environment.NewLine;
              ketqua = ketqua + "using System.Configuration;" + Environment.NewLine;
              ketqua = ketqua + "using System.Security.Cryptography;" + Environment.NewLine;
              ketqua = ketqua + Environment.NewLine;
              ketqua = ketqua + "namespace " + name_space + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "class Algorithm" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "public static string GenerateRandom(int length)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "string allowedLetterChars = \"abcdefghijkmnpqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ\";" + Environment.NewLine;
              ketqua = ketqua + "string allowedNumberChars = \"0123456789\";" + Environment.NewLine;
              ketqua = ketqua + "char[] chars = new char[length];" + Environment.NewLine;
              ketqua = ketqua + "Random rd = new Random();" + Environment.NewLine;
              ketqua = ketqua + "bool useLetter = true;" + Environment.NewLine;
              ketqua = ketqua + "for (int i = 0; i < length; i++)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "if (useLetter)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "chars[i] = allowedLetterChars[rd.Next(0, allowedLetterChars.Length)];" + Environment.NewLine;
              ketqua = ketqua + "useLetter = false;" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "else" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "chars[i] = allowedNumberChars[rd.Next(0, allowedNumberChars.Length)];" + Environment.NewLine;
              ketqua = ketqua + "useLetter = true;" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "return new string(chars);" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "public static string GenerateRandomID()" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "string sDate;" + Environment.NewLine;
              ketqua = ketqua + "string sResult;" + Environment.NewLine;
              ketqua = ketqua + "sDate = (\"00\" + DateTime.Now.Day.ToString()).Substring((\"00\" + DateTime.Now.Day.ToString()).Length - 2) +" + Environment.NewLine;
              ketqua = ketqua + "(\"00\" + DateTime.Now.Month.ToString()).Substring((\"00\" + DateTime.Now.Month.ToString()).Length - 2) +" + Environment.NewLine;
              ketqua = ketqua + "DateTime.Now.Year.ToString();" + Environment.NewLine;
              ketqua = ketqua + "sResult = sDate + GenerateRandom(22);" + Environment.NewLine;
              ketqua = ketqua + "return sResult;" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;

              return ketqua;
           }
           catch(Exception ex)
           {

              XtraMessageBox.Show(ex.Message);
              return "";
           }
        }
        public static string PageBase(string name_space)
        {
           try
           {
              string ketqua = "";
              ketqua = ketqua + "using System;" + Environment.NewLine;
              ketqua = ketqua + "using System.Collections.Generic;" + Environment.NewLine;
              ketqua = ketqua + "using System.Data;" + Environment.NewLine;
              ketqua = ketqua + "using System.IO;" + Environment.NewLine;
              ketqua = ketqua + "using System.Text;" + Environment.NewLine;
              ketqua = ketqua + "using System.Configuration;" + Environment.NewLine;
              ketqua = ketqua + "using System.Windows.Forms;" + Environment.NewLine;
              ketqua = ketqua + "using DevExpress.XtraEditors.Repository;" + Environment.NewLine;
              ketqua = ketqua + Environment.NewLine;
              ketqua = ketqua + "namespace " + name_space + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "class PageBase" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "public static void StateButton(bool sStateButton, DevExpress.XtraBars.BarButtonItem sAddNew, DevExpress.XtraBars.BarButtonItem sEdit, DevExpress.XtraBars.BarButtonItem sSave, DevExpress.XtraBars.BarButtonItem sCancel, DevExpress.XtraBars.BarButtonItem sDelete)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "if (sStateButton == true)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "sAddNew.Enabled = false;" + Environment.NewLine;
              ketqua = ketqua + "sEdit.Enabled = false;" + Environment.NewLine;
              ketqua = ketqua + "sSave.Enabled = true;" + Environment.NewLine;
              ketqua = ketqua + "sCancel.Enabled = true;" + Environment.NewLine;
              ketqua = ketqua + "sDelete.Enabled = false;" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "else" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "sAddNew.Enabled = true;" + Environment.NewLine;
              ketqua = ketqua + "sEdit.Enabled = true;" + Environment.NewLine;
              ketqua = ketqua + "sSave.Enabled = false;" + Environment.NewLine;
              ketqua = ketqua + "sCancel.Enabled = false;" + Environment.NewLine;
              ketqua = ketqua + "sDelete.Enabled = true;" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;

              ketqua = ketqua + "public static void StateButton(bool sStateButton, System.Windows.Forms.ToolStripButton sAddNew, System.Windows.Forms.ToolStripButton sEdit, System.Windows.Forms.ToolStripButton sSave, System.Windows.Forms.ToolStripButton sCancel, System.Windows.Forms.ToolStripButton sDelete)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "if (sStateButton == true)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "sAddNew.Visible = false;" + Environment.NewLine;
              ketqua = ketqua + "sEdit.Visible = false;" + Environment.NewLine;
              ketqua = ketqua + "sSave.Visible = true;" + Environment.NewLine;
              ketqua = ketqua + "sCancel.Visible = true;" + Environment.NewLine;
              ketqua = ketqua + "sDelete.Visible = false;" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "else" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "sAddNew.Visible = true;" + Environment.NewLine;
              ketqua = ketqua + "sEdit.Visible = true;" + Environment.NewLine;
              ketqua = ketqua + "sSave.Visible = false;" + Environment.NewLine;
              ketqua = ketqua + "sCancel.Visible = false;" + Environment.NewLine;
              ketqua = ketqua + "sDelete.Visible = true;" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;


              ketqua = ketqua + "public static void VisibleColumnInGrid(DevExpress.XtraGrid.Views.Grid.GridView grv, bool bValue)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "//foreach (DevExpress.XtraGrid.Columns.GridColumn col in grv.Columns)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "if (bValue == true)" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "//col.OptionsColumn.AllowEdit = true;" + Environment.NewLine;
              ketqua = ketqua + "grv.OptionsBehavior.Editable = true;" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "else" + Environment.NewLine;
              ketqua = ketqua + "{" + Environment.NewLine;
              ketqua = ketqua + "//col.OptionsColumn.AllowEdit = false;" + Environment.NewLine;
              ketqua = ketqua + "grv.OptionsBehavior.Editable = false;" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              ketqua = ketqua + "}" + Environment.NewLine;
              return ketqua;
           }
           catch(Exception ex)
           {
              XtraMessageBox.Show(ex.Message);
              return "";
           }
        }
        private static string InfoTableName(string tablename)
        {
            return System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(tablename.ToLower()) + "Info";
        }
        private static string DALTableName(string tablename)
        {
            return System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(tablename.ToLower()) + "DAL";
        }
        private static string BUSTableName(string tablename)
        {
            return System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(tablename.ToLower()) + "BUS";
        }
    }
}
